

clear
set more 1
set type double


*****************************************************************
*1. prepare phi_xeu


*NOTES:
*there are many firms that trade but that do not have to submit company accounts to the NBB
*this is because they are not incorporated as a limited liability company, but under a different legal form
*this could be individual entrepreneurs, or it could be under the form of some type of cooperatives
*we supplement these data to construct phi for larger sample
*******************************************************************************************

**********************************************
*1. Construct phi
**********************************
*Construct phi variable as average for 2012-2019
****************************************************
****************************************

*i. construct extra-eurozone imports by firm
*********************************************
use ../../rawdata/M_2019, clear	
	append using ../../rawdata/M_2018
	append using ../../rawdata/M_2017
	append using ../../rawdata/M_2016
	append using ../../rawdata/M_2015
	append using ../../rawdata/M_2014
	append using ../../rawdata/M_2013
	append using ../../rawdata/M_2012


	drop if land=="EU"
	gen eurozone = inlist(land, "AT", "CY", "EE", "FI", "FR", "DE", "GR", "IE", "IT") 
	replace eurozone =1 if inlist(land, "LV", "LT", "LU", "MT", "NL", "PT", "SK", "SI", "ES")
	replace eurozone = 1 if inlist(land, "AD", "MC", "SM", "XK", "ME") 
	
	gen value_eu=value*eurozone
	gen value_xeu=value*(1-eurozone)
		
	collapse (sum) mvalue_xeu=value_xeu mvalue_eu=value_eu mvalue_all=value, by(vat year) 
	
	save temp/xeuro_imports2012to2019, replace 
		

*ii. total inputs by firm
*********************************************
** VAT data declarations contain all individuals, firms and legal corporations with a VAT number **
** A substantial fraction of them are not required to submit company accounts **

use ../../rawdata/VAT_quarterly_Data, clear
	keep if year>=2012 & year<=2016
	collapse (sum) inputs, by(vat year)
	save temp/inputs_2012to2016, replace

*add 2017 and 2018
use ../../rawdata/vat_declaration_monthly_updated, clear
	keep if year==2017|year==2018
	collapse (sum) inputs, by(vat year)
	save temp/inputs_2017to2018, replace
	append using temp/inputs_2012to2016
	save temp/inputs_2012to2018, replace

*add in	2019 
use ../../rawdata/update_feb21/monthly&quarterly_salesVAT_2019_2020.dta, clear
	keep if year==2019
	collapse (sum) inputs, by(vat year)
	append using temp/inputs_2012to2016
	append using temp/inputs_2017to2018
save temp/inputs_2012to2019, replace


**iii. employment and wages data based taken from RSZ (social security agency), quarterly
********************************************************************************
use ../../rawdata/RSZ_kwartaal,clear
	rename kbo_nr vat
	tostring kwartaal, replace
	gen year=substr(kwartaal,1,4)
	gen quarter=substr(kwartaal,5,1)
	destring year,replace
	destring quarter, replace
	rename vte emp
	label var emp "full time equivalent number of employees
	
	rename brutolonen grosswage
	label var grosswage "gross wage bill" 
	replace grosswage=grosswage*1000 
	gen wagebill=grosswage
		
	keep if year>=2012 & year<=2016
	keep vat emp wagebill year quarter
	duplicates drop
	drop if vat==0
	drop if wagebill==0

	collapse (sum) emp wagebill, by(vat year quarter)
	
	save temp/emp_2012to2016,replace
	
*add 2017 and 2018 data
 	use ../../rawdata/RSZupdate, clear
 	keep if year==2017|year==2018
 	rename quarter date
 	rename fte emp
 	gen quarter=quarter(dofq(date))
 	drop date
 	duplicates drop
	drop if vat==0
	rename brutolonen grosswage
	replace grosswage=grosswage*1000 
	gen wagebill=grosswage
	drop if wagebill==0
		
 	collapse (sum) emp wagebill, by(vat year quarter)
 	 	
	append using temp/emp_2012to2016
	save temp/emp_2012to2018,replace
	
	
*add 2019 data
	use ../../rawdata/update_feb21/RSZupdate_2021, clear
	keep if year==2019
	drop emp
	rename quarter date
	rename fte emp
	gen quarter=quarter(dofq(date))
	drop date
	duplicates drop
	drop if vat==0
	rename wage_gross grosswage
	replace grosswage=grosswage*1000 
	gen wagebill=grosswage
	drop if wagebill==0

	collapse (sum) emp wagebill, by(vat year quarter)

	append using temp/emp_2012to2018

	preserve
		collapse (mean) emp (sum) wagebill, by(vat year)
		save ../data/emp_wages_annual_2012to2019, replace
	restore	

		collapse (mean) memp=emp, by(vat)
		label var memp "average employees between 2012 and 2019"
	save ../data/memp, replace


*iv. combine inputs and wages and only keep nonzero and nonmissing overlap of both
**************************************************************************************
	use ../data/emp_wages_annual_2012to2019, clear
	merge 1:1 vat year using temp/inputs_2012to2019, keep(match) nogen
	drop if missing(wagebill)|missing(inputs)
	drop if emp==0
	drop if wagebill==0|inputs==0

*cleaning
*i. clean wage bill on average wage
	gen avwage=wagebill/emp
	egen w1=pctile(avwage), p(1)
	egen w99=pctile(avwage), p(99)
	gen cleanw=avwage<w1|avwage>w99
	drop if cleanw==1
	
	gen tvc=wagebill+inputs
	gen inputsh=inputs/tvc
	bys vat (year): egen med_inputsh=median(inputsh)
	gen ratio_inputsh=inputsh/med_inputsh
	egen i1=pctile(ratio_inputsh), p(1)
	egen i99=pctile(ratio_inputsh), p(99)
	gen cleani=ratio_inputsh<i1|ratio_inputsh>i99
	drop if cleani==1
	
	save temp/tc_allyears, replace


*v. indicator of missing inputs for imports (45.9% missing obs, 55% of imports)
*********************************************************
	use temp/xeuro_imports2012to2019, clear
		merge 1:1 vat year using temp/tc_allyears, keep(match master) nogen
		gen phi_xeu=mvalue_xeu/(wagebill+inputs)
		gen over=phi_xeu>=1 & !missing(phi_xeu)
		replace phi_xeu=. if phi>=1 & !missing(phi)
		
	save temp/imports_tc_allyears, replace
		collapse (mean) mphi_xeu=phi_xeu, by(vat)
		label var mphi_xeu "average of phi_xeu between 2012 and 2019"
		drop if missing(mphi_xeu)
	save ../data/mphi_xeu, replace	

		

 